package database;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import shared.Pair;


import log.Log;
import log.Log.priority;

public class DatabaseClient 
{	
	// CHANGE THAT TO YOUR LOCAL PARAMETERS
	private final static String DB_NAME = "FACEBOOK";
	private final static String DB_USER = "Tomasz";	
	private final static String DB_PASS = "Tomasz";	
	
	private final static String CONNECTION_URL = "jdbc:sqlserver://localhost:1433;" +
			"databaseName="+DB_NAME+";user="+DB_USER+";password="+DB_PASS+";integratedSecurity=false;";
	private Connection connection = null;
	private Statement statement = null;
	private PreparedStatement prepStatement = null;
	private ResultSet resultSet = null;
	
	public void connect()
	{
		try 
		{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			connection = DriverManager.getConnection(CONNECTION_URL);
		}
		catch (Exception e) 
		{			
			Log.console("Connection to DB failed", priority.Error);
			Log.console(e.getMessage(), priority.Error);
		}
	}
	
	public ResultSet executeQuery(String query)
	{
		try
		{
			statement = connection.createStatement();		
			resultSet = statement.executeQuery(query);
			return resultSet;
		} 
		catch (SQLException e) 
		{
			Log.console(e.getMessage(), priority.Error);
			return null;
		}						
	}
	
	public void executeQueryWithoutResult(String query)
	{
		try
		{
			statement = connection.createStatement();
			statement.executeQuery(query);
		} 
		catch (SQLException e) 
		{
			Log.console(e.getMessage(), priority.Error);
		}						
	}
	
	public ResultSet executeParameterizedQuery(String query, ArrayList<Pair<String, Class<?>>> arrayList)
	{		
		try
		{
			prepStatement = connection.prepareStatement(query);	
			for(int i=0;i<arrayList.size();i++)
			{
				if(arrayList.get(i).getValue() == Long.class)
				{
					if(arrayList.get(i).getKey().isEmpty())
						prepStatement.setLong(i+1, 0);
					else
						prepStatement.setLong(i+1, Long.valueOf(arrayList.get(i).getKey()));
				}
				
				else if(arrayList.get(i).getValue() == Integer.class)
				{
					if(arrayList.get(i).getKey().isEmpty())
						prepStatement.setInt(i+1, 0);
					else
						prepStatement.setInt(i+1, Integer.valueOf(arrayList.get(i).getKey()));
				}
				
				else // STRING
				{
					if(arrayList.get(i).getKey().isEmpty())
						prepStatement.setString(i+1, "NULL");
					else
						prepStatement.setString(i+1, String.valueOf(arrayList.get(i).getKey()));
				}
			} 
			resultSet = prepStatement.executeQuery();
			return resultSet;
		} 
		catch (SQLException e) 
		{
			Log.console(e.getMessage(), priority.Error);
			return null;
		}	
	}
	
	public void executeParameterizedQueryWithoutResult(String query, ArrayList<Pair<String, Class<?>>> arrayList)
	{
		try
		{	
			prepStatement = connection.prepareStatement(query);	
			for(int i=0;i<arrayList.size();i++)
			{
				if(arrayList.get(i).getValue() == Long.class)
				{   
					if(arrayList.get(i).getKey().isEmpty())
						prepStatement.setLong(i+1, 0); 
					else
						prepStatement.setLong(i+1, Long.valueOf(arrayList.get(i).getKey()));
				}
				
				else if(arrayList.get(i).getValue() == Integer.class)
				{
					if(arrayList.get(i).getKey().isEmpty())
						prepStatement.setInt(i+1, 0);
					else
						prepStatement.setInt(i+1, Integer.valueOf(arrayList.get(i).getKey()));
				}				
				else // STRING
				{
					if(arrayList.get(i).getKey().isEmpty())
						prepStatement.setString(i+1, String.valueOf("NULL"));
					else
						prepStatement.setString(i+1, String.valueOf(arrayList.get(i).getKey()));
				}
			}
			prepStatement.execute();
		} 
		catch (SQLException e) 
		{
			Log.console(e.getMessage(), priority.Error);
		}	
	}
	
	public void disconnect()
	{
		if (resultSet != null)
		{
			try 
			{
				resultSet.close();
			} 
			catch (SQLException e)
			{
				Log.console(e.getMessage(), priority.Error);
			}
			resultSet = null;
		}
		
		if (statement != null) 
		{
			try 
			{
				statement.close();
			} 
			catch (SQLException e)
			{
				Log.console(e.getMessage(), priority.Error);
			}
			statement = null;
		}
		
		if (prepStatement != null) 
		{
			try 
			{
				prepStatement.close();
			} 
			catch (SQLException e)
			{
				Log.console(e.getMessage(), priority.Error);
			}
			prepStatement = null;
		}
		
		
		if (connection != null) 
		{
			try 
			{
				connection.close();
			} 
			catch (SQLException e)
			{
				Log.console(e.getMessage(), priority.Error);
			}
			connection = null;
		}
	}
}
